﻿CREATE PROCEDURE [dbo].[System_ExpandDatePartitionFunction]
	@PartitionFunction NVARCHAR(256),
	@NextFileGroup NVARCHAR(256) = NULL,
	@NewFileGroupFolderPath NVARCHAR(MAX) = NULL
AS
	DECLARE @CurrentValue DATETIME
	DECLARE @Range NVARCHAR(10)

	Select --pf.name [PartitionFunctionName]
		--, t.name [PartitionDataType]
		--, CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' ELSE 'LEFT' END [BoundaryValue]
		--, MAX(pfrv.boundary_id) [PartitionNumber]
		 @CurrentValue = MAX(CAST(pfrv.value AS datetime)) 
		, @Range = SUBSTRING(pf.name,CHARINDEX('_', pf.name, CHARINDEX('_', pf.name) + 1)+ 1, 10)
	From sys.partition_functions pf
	INNER JOIN sys.partition_range_values pfrv
	 ON pf.function_id = pf.function_id 
	INNER JOIN sys.partition_parameters pp
	 ON pp.function_id = pf.function_id 
	INNER JOIN sys.types t
	 ON t.user_type_id = pp.user_type_id 
	WHERE pf.name = 'PF_DateRange_Year'
	GROUP BY SUBSTRING(pf.name,CHARINDEX('_', pf.name, CHARINDEX('_', pf.name) + 1)+ 1, 10)
 
	DECLARE @Sql NVARCHAR(MAX)
	DECLARE @Param NVARCHAR(MAX)
	SET @Sql = 'SELECT @Value= DATEADD(' + @RANGE +', 1, ''' + CONVERT(VARCHAR(50), @CurrentValue, 120) + ''')'
	SET @Param = '@Value DATETIME OUTPUT'
	DECLARE @NewValue DATETIME 
 
	 EXEC sp_executesql @Sql, @Param, @Value = @NewValue OUTPUT
 
	--Now we have a new Max Value to use to expand the function.
	PRINT @CurrentValue
	Print @NewValue
	DECLARE @SplitCmd NVARCHAR(MAX)
	SET @SplitCmd = 'ALTER PARTITION FUNCTION SPLIT RANGE ( ' + CONVERT(VARCHAR(50), @NewValue, 101) +')'
	
	BEGIn TRANSACTION
	BEGIN TRY
		--Now we need to Deal with the Next FileGroup before we actually Split the Partition Function
		IF @NextFileGroup IS NOT NULL
		BEGIN
			--IF NOT EXISTS (SELECT 1
			--			FROM sys.fileGroups
			--			WHERE name = @NextFileGroup)
			--BEGIN
			--	--OK the fileGroup needs to be created, see if a Path was given
			--	IF @NewFileGroupFolderPath IS NULL
			--	BEGIN
			--		RAISERROR 65535 'System_ExpandDatePartitionFunction: @NewFileGroupFolderPath can not be NULL when FileGroup Supplied in @NextFileGroup does not exist.'
			--		RETURN -1

			--	END
			--	--Ok So create the File Groups
			--	DECLARE @AddFileGroup NVARCHAR(MAX)
			--	ALTER DATABASE DB_NAME() ADD FILEGROUP [ @NextFileGroup ]

			--	ALTER DATABASE DB_NAME() ADD FILE
			--	(
			--		NAME = [FG_@NextFileGroup_1],
			--		FILENAME = '@NewFileGroupFolderPath \FG_@NextFileGroup_1.ndf',
			--		SIZE = 3072 KB, 
			--		MAXSIZE = UNLIMITED, 
			--		FILEGROWTH = 1024 KB
			--	) TO FILEGROUP [@NextFileGroup]
			--END
			PRINT '1'
		END

		EXEC sp_ExecuteSQL @SplitCmd

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
		DECLARE @msg NVARCHAR(MAX)
		SET @msg = ERROR_MESSAGE()
		RAISERROR 65535 @msg
	END CATCH


RETURN 0